#include "databasemanager.h"
#include "abstractstorage.h"
#include "abstractcontainer.h"
#include "defines.h"
#include "storenums.h"
#include "tableinfo.h"
#include "datatreehelper.h"
#include "workbench.h"

#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlField>
#include <QSqlDatabase>
#include <QVariant>
#include <QMetaEnum>
#include <QMessageBox>
#include <QStringList>
#include <QXmlStreamReader>
#include <QXmlStreamWriter>
#include <QSet>
#include <QHash>

DatabaseManager* DatabaseManager::m_DatabaseManager = 0;

DatabaseManager::DatabaseManager() : m_databasesContainer(0), QObject()
{

}

DatabaseManager::~DatabaseManager()
{

}

DatabaseManager * DatabaseManager::instance()
{
    if(m_DatabaseManager == 0)
        m_DatabaseManager = new DatabaseManager();

    return m_DatabaseManager;
}

bool DatabaseManager::initDatabases(AbstractContainer *databases)
{
    bool res = true;
    m_databasesContainer = databases;
    if(databases->size() == 1) {
        AbstractStorage* db = databases->at(0);
        db->setProperty(XML_DATABASENAME, QLatin1String(QSqlDatabase::defaultConnection));
        initDatabase(db);
    }
    else
    for(int i = 0; i < databases->size() && res; i++) {
        AbstractStorage* db = databases->at(i);
        initDatabase(db);
    }
    return res;
}

bool DatabaseManager::initDatabase(AbstractStorage *database)
{
    bool res = true;    
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", database->property(XML_DATABASENAME).toString());
    QString dbConnString = database->property(XML_CONNECTIONSTRING).toString();
    if( dbConnString.contains('/') == false && dbConnString.contains('\\') == false )
        dbConnString = Workbench::instance()->projectFolder() + dbConnString;
    db.setDatabaseName(dbConnString);

    if ( !db.open() ) {
        QMessageBox::critical(0, tr("Ошибка"),
        tr("Невозможно открыть базу данных.\n")+dbConnString, QMessageBox::Ok);
        return false;
    }

    AbstractContainer* tables = database->containerByName(XML_TABLES);
    for(int i = 0; i<tables->size() && res; i++)
        res = initTable(tables->at(i),db);    

    return res;
}

bool DatabaseManager::initTable(AbstractStorage *table, const QString &dbName)
{
    QSqlDatabase db = QSqlDatabase::database(dbName);
    return initTable(table,db);
}

bool DatabaseManager::initTable(AbstractStorage *table, QSqlDatabase &db)
{
    bool res = db.isValid();
    if(res) {
        QString tableName = table->property(XML_TABLENAME).toString();
        if( !isTableExist(tableName, db) ) {
            res = createTable(table, db);
        }
        else { // Check table structure
            TableInfo info(tableName,db);
            if( info.isValid() ) {
                updateTableStruct(table, &info, db);
            }
        }
    }
    return res;
}

bool DatabaseManager::isTableExist(const QString &tableName, const QSqlDatabase &db)
{
    bool res = false;
    if( db.driverName().compare("QSQLITE") == 0 ) {
        QSqlQuery query(db);
        query.exec("select name from sqlite_master where name = '" + tableName +"'");
        if( query.first() )
            res = true;
    }
    else {
        QSqlQuery query(db);
        query.exec( "select * from "+tableName + " where 1=2" );
        res = query.isValid();
    }
    return res;
}

bool DatabaseManager::createTable(AbstractStorage *table, QSqlDatabase &db)
{
    bool res = true;
    AbstractContainer* columns = table->containerByName(XML_COLUMNS);
    QString tableName = table->property(XML_TABLENAME).toString();
    if(columns->size()>0) {
        QString sql = "create table  "+tableName + " ( ";
        for(int i = 0; i < columns->size(); i++) {
            AbstractStorage *column = columns->at(i);
            QString colName = column->property(XML_COLUMNNAME).toString();
            int length = column->property(XML_COLUMNLENGTH).toInt();
            bool primary = column->property(XML_ISPRIMARYKEY).toBool();
            QString type = column->property(XML_COLUMNTYPE).toString();
            QString dflt_value = column->property(PROP_DEFAULTVALUE).toString();
            sql += colName + " ";

            if(length > 0)
                sql += type + " ( " + QString::number(length) + " )";
            else
                sql += type + "";

            if( dflt_value.compare(NULLSTRING) != 0 )
                sql += " DEFAULT('" + dflt_value + "')";

            if(primary)
                sql += " primary key";

            if( i < columns->size()-1 )
                sql += ",";
        }
        sql += " )";
        QSqlQuery query(db);
        res = query.exec(sql);
    } else {
        res = false;
        QMessageBox::critical(0,"Error","Table " + tableName + " has no columns" );
    }
    return res;
}

#include <QList>
void DatabaseManager::updateTableStruct(AbstractStorage *table, TableInfo* oldStruct, QSqlDatabase &db)
{
    if( table && oldStruct )
    {
        QList<int> appendColumns;   // 0
        QList<int> removeColumns;   // 1
        QList<int> changeColumns;   // 2
        QStringList keepColumns;

        AbstractContainer *columns = table->containerByName(XML_COLUMNS);

        int i = 0, j = 0;

        for( i = 0; i < columns->size(); i++ )
            appendColumns.append(i);

        for( j = 0; j < oldStruct->columnsCount(); j++ )
            removeColumns.append(j);

        i = 0;j = 0;
        while( i < appendColumns.size() )
        {
            j = 0;
            while( j < removeColumns.size() && i >= 0 )
            {
                QString colName = oldStruct->columnName( removeColumns[j] );
                QString newName = columns->at( appendColumns[i] )->property(XML_COLUMNNAME).toString();
                if( colName.compare(newName) == 0 )
                {
                    QString type = columns->at( appendColumns[i] )->property(XML_COLUMNTYPE).toString();
                    if( type.compare( oldStruct->columnType( removeColumns[j] ) ) != 0 )
                    {                        
                        changeColumns.append( removeColumns[j] );
                    }
                    appendColumns.removeAt(i);
                    removeColumns.removeAt(j);
                    keepColumns << colName;
                    j--;
                    i--;
                    break;
                }
                j++;
            }
            i++;
        }


//        qDebug()<<"------------"<<table->property(XML_TABLENAME).toString()<<"------------";
//        for( int i = 0; i < changeColumns.size(); i++ )
//        {
//            qDebug() << "CHANGE " << oldStruct->columnName( changeColumns[i] );
//        }
//        for( int i = 0; i < appendColumns.size(); i++ )
//        {
//            qDebug() << "APPEND " << columns->at( appendColumns[i] )->property(XML_COLUMNNAME).toString();
//        }
//        for( int i = 0; i < removeColumns.size(); i++ )
//        {
//            qDebug() << "REMOVE " << oldStruct->columnName( removeColumns[i] );
//        }
        if( removeColumns.size() > 0 || changeColumns.size() > 0 || appendColumns.size() > 0 )
        {
            QString tableName = table->property(XML_TABLENAME).toString();

            db.transaction();

            bool b;
            b = renameTable(tableName, tableName + "_temp", db);
            if( b )
                initTable(table, db);
            if( b )
                b = copyTableData(tableName + "_temp", tableName, db, keepColumns);
            if( b )
                dropTable( tableName + "_temp",db );
            db.commit();
        }
    }
}

bool DatabaseManager::renameTable(const QString &origName, const QString &newName, QSqlDatabase &db)
{
    QSqlQuery query(db);
    return query.exec( "ALTER TABLE " + origName + " RENAME TO " + newName );
}

bool DatabaseManager::copyTableData(const QString &srcTable, const QString &destTable, QSqlDatabase &db, const QStringList &columns)
{
    QSqlQuery query(db);
    QString sql = "INSERT INTO ";
    sql += destTable + " (";
    QString cols = "";
    for( int i = 0; i < columns.size(); i++ ) {
        cols += columns[i];
        if( i < columns.size() - 1 )
            cols += ", ";
    }
    sql += cols + ")";
    sql += " SELECT " + cols + " FROM " + srcTable;
    return query.exec(sql);
}

bool DatabaseManager::dropTable(const QString &tableName, QSqlDatabase &db)
{
    QSqlQuery query(db);
    return query.exec("DROP TABLE "+tableName);
}
#include <QFile>
bool DatabaseManager::updateTables(QIODevice *source, QSqlDatabase &db, const QString &deleteFilter)
{
    bool res = true, bFilter = deleteFilter.length() > 0;
    int level = 0;
    QSqlQuery query(db);

    QSet<QString> tables;
    QString table,col,val;
    QStringList columns;
    QStringList values;
    QStringList thatDbColumns;    
    QSet<QString> thatDbColumnsSet;
    QStringList filterList;
    QStringList filterColumns;
    QStringList filterValues;

    if(bFilter) {
        filterList = deleteFilter.split(" and ", QString::SkipEmptyParts, Qt::CaseInsensitive);
        for( int i = 0; i < filterList.size(); i++ ) {
            QStringList keyVal = filterList.at(i).split('=');
            if(keyVal.size() == 2) {
                filterColumns.append( keyVal.at(0).trimmed() );
                filterValues.append( keyVal.at(1).trimmed() );
            }
        }
    }

    AbstractStorage *dbStruct = 0;
    AbstractContainer *dbTables = 0;
    if(m_databasesContainer != 0) {                                     // Find database signature

        if(m_databasesContainer->size()==1) {
            dbStruct = m_databasesContainer->at(0);
        } else {
            for(int i = 0; i < m_databasesContainer->size(); i++ ) {
                if( db.databaseName().compare( m_databasesContainer->at(i)->property(XML_DATABASENAME).toString()) == 0  ) {
                    dbStruct = m_databasesContainer->at(i);
                    break;
                }
            }
        }
    }
    Q_ASSERT(dbStruct);
    dbTables = dbStruct->containerByName(XML_TABLES);
    db.transaction();

    if(source->isOpen())
        source->reset();
    else
        source->open(QIODevice::ReadOnly);

    QXmlStreamReader xml(source);

    while( !xml.atEnd() && res ) {
        xml.readNext();
        if(xml.isStartElement()) {
            if(level==2) {
                table = xml.name().toString();
                if( !tables.contains(table) ) {
                    thatDbColumns.clear();
                    tables.insert(table);
                    QString delSql = "Delete From " + table;
                    if(bFilter)
                        delSql += " Where " +deleteFilter;
                    query.exec(delSql);
                    // We suppouse tables not mixing rows
                    thatDbColumns.clear();
                    int tableNo = -1;
                    for(int i = 0; i < dbTables->size(); i++) {
                        if(dbTables->at(i)->property(XML_TABLENAME).toString().compare(table,Qt::CaseInsensitive) == 0) {
                            tableNo = i;
                            break;
                        }
                    }
                    if(tableNo >= 0) {
                        AbstractContainer* tblColumns = dbTables->at(tableNo)->containerByName(XML_COLUMNS);
                        for(int i = 0; i < tblColumns->size(); i++)
                            thatDbColumns.append( tblColumns->at(i)->property(XML_COLUMNNAME).toString().toUpper() );
                        thatDbColumnsSet = thatDbColumns.toSet();
                    }
                }
            } else if(level==3) {
                col = xml.name().toString().toUpper();
                val = xml.readElementText();
                values.append( val );
                columns.append( col );
            }
            ++level;
        }
        if( xml.isEndElement() ) {
            if(level==3) {
                if(thatDbColumns.size()>0) {
                    QString sql = "INSERT INTO " + table + " (";

                    for( int i = 0; i < columns.size(); i++ ) {
                        if( thatDbColumnsSet.contains( columns.at(i) ) )
                        sql += columns.at(i) + ",";
                    }
                    for( int i = 0; i < filterColumns.size(); i++)
                        sql += filterColumns.at(i) + ",";

                    sql.replace(sql.length()-1,1,')');
                    sql += " VALUES (";
                    for( int i = 0; i < values.size(); i++ ) {
                        if( thatDbColumnsSet.contains( columns.at(i) ) )
                        sql += "\'"+values.at(i) + "\'" +",";
                    }
                    for( int i = 0; i < filterValues.size(); i++)
                        sql += filterValues.at(i) + ",";
                    sql.replace(sql.length()-1,1,')');

                    query.exec(sql);
                }
                columns.clear();
                values.clear();
            }
            --level;
        }
    }
    db.commit();
    if(xml.error() != QXmlStreamReader::NoError) {
//        QFile *file = (QFile*) source;
//        qDebug() << xml.errorString() << xml.lineNumber() << file->fileName();
        res = false;
    }
    Q_ASSERT(xml.error() == QXmlStreamReader::NoError);
    return res;
}

bool DatabaseManager::tableToXml(QXmlStreamWriter *pXml, DataTreeHelper *dtHelper)
{
    bool res = true;
    QString sql = "SELECT ";
    QString tableName = dtHelper->getTableName();
    QString dbName = dtHelper->getDatabaseName();
    QString filter = dtHelper->getFilter();
    QStringList fields = dtHelper->getFields();

    QSqlDatabase db;
    if(dbName.length() > 0)
        db = QSqlDatabase::database(dbName);
    else
        db = QSqlDatabase::database();
    QSqlQuery query(db);

    if(fields.size()>0) {
        for(int i = 0; i< fields.size(); i++) {
            if(i!=0)
                sql+=",";
            sql+=fields.at(i);
        }
    } else
        sql += "*";
    sql += " FROM " + tableName;
    if(filter.size()>0)
        sql += " WHERE " + filter;
    res = query.exec(sql);
    if(res) {
        bool move = query.first();
        if(move) {           
            while(move) {
                pXml->writeStartElement(tableName);
                QSqlRecord rec = query.record();
                int fieldCount = rec.count();
                for( int i = 0; i < fieldCount; i++) {
                    pXml->writeTextElement(rec.field(i).name().toUpper(),rec.value(i).toString());
                }
                move = query.next();
                pXml->writeEndElement();
            }            
        }
    }
    return res;
}
#include <QDebug>
bool DatabaseManager::dataTreeToXml(QXmlStreamWriter *pXml, DataTreeHelper *dtHelper, const QString filterField)
{
    bool res = false;
    QStringList ids = dtHelper->getIdentificators();
    if(ids.size()>0) {
        QString sql = "SELECT ";
        QString tableName = dtHelper->getTableName();
        QString dbName = dtHelper->getDatabaseName();
        QStringList fields = dtHelper->getFields();

        if(fields.size()>0) {
            for(int i = 0; i< fields.size(); i++) {
                if(i!=0)
                    sql+=",";
                sql+=fields.at(i);
            }
        } else
            sql += "*";
        sql += " FROM " + tableName;

        QSqlDatabase db;
        if(dbName.length() > 0)
            db = QSqlDatabase::database(dbName);
        else
            db = QSqlDatabase::database();
        QSqlQuery query(db);

        res = true;
        if(ids.size() > 0) {
            pXml->writeStartElement(dtHelper->getTableName());
            pXml->writeAttribute("type", "array");
            for(int i = 0; i < ids.size() && res; i++) {
                res = query.exec(sql + " Where " + filterField + " = " +ids.at(i));
                pXml->writeStartElement(XML__REC);
                if(query.first()) {
                    QSqlRecord rec = query.record();
                    for(int j = 0; j < rec.count(); j++)
                        pXml->writeTextElement(rec.field(j).name(),rec.value(j).toString());
                }
                DatabaseManager::childsToXml(pXml, dtHelper, ids.at(i));
                pXml->writeEndElement(); // REC
            }
            pXml->writeEndElement();
        }
    }

    return res;
}

bool DatabaseManager::childsToXml(QXmlStreamWriter *pXml, DataTreeHelper *dtHelper, QString id)
{
    bool res = true;
    for( int i = 0; i < dtHelper->childsCount(); i++ ) {
        DataTreeHelper *childData = dtHelper->childById(i);

        QString sql = "SELECT ";
        QString tableName = childData->getTableName();
        QString dbName = childData->getDatabaseName();
        QStringList fields = childData->getFields();
        if(fields.size()>0) {
            for(int i = 0; i< fields.size(); i++) {
                if(i!=0)
                    sql+=",";
                sql+=fields.at(i);
            }
        } else
            sql += "*";
        sql += " FROM " + tableName;
        sql += " WHERE mid = " + id;

        QSqlDatabase db;
        if(dbName.length() > 0)
            db = QSqlDatabase::database(dbName);
        else
            db = QSqlDatabase::database();
        QSqlQuery query(db);
        res = query.exec(sql);
        bool next = query.first();
        if( next ) {
            pXml->writeStartElement(tableName);
            pXml->writeAttribute("type","array");
            while( next ) {
                pXml->writeStartElement(XML__REC);
                QSqlRecord rec = query.record();
                for(int j = 0; j < rec.count(); j++)
                    pXml->writeTextElement(rec.field(j).name(),rec.value(j).toString());

                DatabaseManager::childsToXml(pXml, childData, rec.value("id").toString());
                pXml->writeEndElement();
                next = query.next();
            }
            pXml->writeEndElement();//  TableName
        }
    }

    return res;
}
